<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- saved from url=(0028)http://jdbi.org/archive.html -->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>


<title>abacus-common</title>

<script type="text/javascript" src="./css/ga.js"></script>
<script type="text/javascript">
	if (top.location != self.location)
		top.location.replace(self.location);
</script>

<link href="./css/css" rel="stylesheet" type="text/css"></link>
<link href="./css/css(1)" rel="stylesheet" type="text/css"></link>
<link rel="stylesheet" href="./css/syntax.css" type="text/css"></link>
<link rel="stylesheet" href="./css/screen.css" type="text/css"></link>

<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-65247258-1', 'auto');
  ga('send', 'pageview');
</script>

</head>
<body>
	<div id="site">
		<h1>
			<a href="./index.html">abacus-common</a> <span id="navigation"> <a href="./docs.html">Docs</a> | <a href="./download.html">Download</a>
				| <a href="./api-docs/index.html">Javadoc</a> | <a href="./faq.html">FAQ</a> | <a href="./community.html">Community</a>
			</span>
		</h1>
	</div>

	<div id="content">

		<div id="page">

			<h1>SQLiteExecutor</h1>
			<p>
				<a href="./SQLiteExecutor_view.html">SQLiteExecutor</a> is a wrapper of SQLiteDatabase.
				Just like SQLExecutor, it makes things much easier to operate data with SQLite on Android by the straight support of Entity/Map/Query...,
				and almost all the java basic types: boolean...double...Date...String...Map...Entity.... Here is a simple sample, comparing to the implementation by SQLiteDatabase:			
			</p>
			
			<li>CRUD by SQLiteExecutor</li>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%"><span style="color: #AA22FF">@Test</span>
<span style="color: #008000; font-weight: bold">public</span> <span style="color: #B00040">void</span> <span style="color: #0000FF">test_crudBySQLiteExecutor</span><span style="color: #666666">()</span> <span style="color: #666666">{</span>
    Account account <span style="color: #666666">=</span> createAccount<span style="color: #666666">();</span>

    <span style="color: #408080; font-style: italic">// create</span>
    <span style="color: #B00040">long</span> id <span style="color: #666666">=</span> sqliteExecutor<span style="color: #666666">.</span><span style="color: #7D9029">insert</span><span style="color: #666666">(</span>account<span style="color: #666666">);</span>

    <span style="color: #408080; font-style: italic">// read</span>
    String sql_getById <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> FIRST_NAME<span style="color: #666666">,</span> LAST_NAME<span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">class</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> id<span style="color: #666666">)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
    Account dbAccount <span style="color: #666666">=</span> sqliteExecutor<span style="color: #666666">.</span><span style="color: #7D9029">queryForEntity</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">class</span><span style="color: #666666">,</span> sql_getById<span style="color: #666666">,</span> id<span style="color: #666666">);</span>

    <span style="color: #408080; font-style: italic">// update        </span>
    dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setFirstName</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;newFirstName&quot;</span><span style="color: #666666">);</span>
    sqliteExecutor<span style="color: #666666">.</span><span style="color: #7D9029">update</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">,</span> dbAccount<span style="color: #666666">,</span> L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> id<span style="color: #666666">));</span>

    <span style="color: #408080; font-style: italic">// delete</span>
    sqliteExecutor<span style="color: #666666">.</span><span style="color: #7D9029">delete</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">,</span> L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>FIRST_NAME<span style="color: #666666">,</span> dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">getFirstName</span><span style="color: #666666">()));</span>

    <span style="color: #408080; font-style: italic">// check</span>
    dbAccount <span style="color: #666666">=</span> sqliteExecutor<span style="color: #666666">.</span><span style="color: #7D9029">queryForEntity</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">class</span><span style="color: #666666">,</span> sql_getById<span style="color: #666666">,</span> id<span style="color: #666666">);</span>
    assertNull<span style="color: #666666">(</span>dbAccount<span style="color: #666666">);</span>
<span style="color: #666666">}</span>
</pre></div>

	<p></p>
	<li>CRUD by SQLiteDatabase</li>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%"><span style="color: #AA22FF">@Test</span>
<span style="color: #008000; font-weight: bold">public</span> <span style="color: #B00040">void</span> <span style="color: #0000FF">test_crudBySQLiteDatabase</span><span style="color: #666666">()</span> <span style="color: #666666">{</span>
    Account account <span style="color: #666666">=</span> createAccount<span style="color: #666666">();</span>

    ContentValues contentValues <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">new</span> ContentValues<span style="color: #666666">();</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;gui&quot;</span><span style="color: #666666">,</span> account<span style="color: #666666">.</span><span style="color: #7D9029">getGUI</span><span style="color: #666666">());</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;first_name&quot;</span><span style="color: #666666">,</span> account<span style="color: #666666">.</span><span style="color: #7D9029">getFirstName</span><span style="color: #666666">());</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;last_name&quot;</span><span style="color: #666666">,</span> account<span style="color: #666666">.</span><span style="color: #7D9029">getLastName</span><span style="color: #666666">());</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;last_update_time&quot;</span><span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">stringOf</span><span style="color: #666666">(</span>account<span style="color: #666666">.</span><span style="color: #7D9029">getLastUpdateTime</span><span style="color: #666666">()));</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;create_name&quot;</span><span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">stringOf</span><span style="color: #666666">(</span>account<span style="color: #666666">.</span><span style="color: #7D9029">getCreateTime</span><span style="color: #666666">()));</span>

    <span style="color: #408080; font-style: italic">// create</span>
    <span style="color: #B00040">long</span> id <span style="color: #666666">=</span> sqliteDatabase<span style="color: #666666">.</span><span style="color: #7D9029">insert</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">,</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">,</span> contentValues<span style="color: #666666">);</span>

    Account dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    <span style="color: #408080; font-style: italic">// read</span>
    String sql_getById <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> FIRST_NAME<span style="color: #666666">,</span> LAST_NAME<span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">class</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> id<span style="color: #666666">)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
    Cursor cursor <span style="color: #666666">=</span> sqliteDatabase<span style="color: #666666">.</span><span style="color: #7D9029">rawQuery</span><span style="color: #666666">(</span>sql_getById<span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">asArray</span><span style="color: #666666">(</span>N<span style="color: #666666">.</span><span style="color: #7D9029">stringOf</span><span style="color: #666666">(</span>id<span style="color: #666666">)));</span>
    <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
        <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">moveToNext</span><span style="color: #666666">())</span> <span style="color: #666666">{</span>
            dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">new</span> Account<span style="color: #666666">();</span>
            <span style="color: #B00040">int</span> columnIndex <span style="color: #666666">=</span> <span style="color: #666666">0;</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setId</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getLong</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setFirstName</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setLastName</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
        <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">else</span> <span style="color: #666666">{</span>
            dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">finally</span> <span style="color: #666666">{</span>
        cursor<span style="color: #666666">.</span><span style="color: #7D9029">close</span><span style="color: #666666">();</span>
    <span style="color: #666666">}</span>

    <span style="color: #408080; font-style: italic">// update        </span>
    dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setFirstName</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;newFirstName&quot;</span><span style="color: #666666">);</span>

    contentValues <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">new</span> ContentValues<span style="color: #666666">();</span>
    contentValues<span style="color: #666666">.</span><span style="color: #7D9029">put</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;first_name&quot;</span><span style="color: #666666">,</span> dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">getFirstName</span><span style="color: #666666">());</span>
    sqliteDatabase<span style="color: #666666">.</span><span style="color: #7D9029">update</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">,</span> contentValues<span style="color: #666666">,</span> <span style="color: #BA2121">&quot;id = ?&quot;</span><span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">asArray</span><span style="color: #666666">(</span>N<span style="color: #666666">.</span><span style="color: #7D9029">stringOf</span><span style="color: #666666">(</span>id<span style="color: #666666">)));</span>

    <span style="color: #408080; font-style: italic">// delete</span>
    sqliteDatabase<span style="color: #666666">.</span><span style="color: #7D9029">delete</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;first_name = ?&quot;</span><span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">asArray</span><span style="color: #666666">(</span>dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">getFirstName</span><span style="color: #666666">()));</span>

    <span style="color: #408080; font-style: italic">// check</span>
    cursor <span style="color: #666666">=</span> sqliteDatabase<span style="color: #666666">.</span><span style="color: #7D9029">rawQuery</span><span style="color: #666666">(</span>sql_getById<span style="color: #666666">,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">asArray</span><span style="color: #666666">(</span>N<span style="color: #666666">.</span><span style="color: #7D9029">stringOf</span><span style="color: #666666">(</span>id<span style="color: #666666">)));</span>

    <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
        <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">moveToNext</span><span style="color: #666666">())</span> <span style="color: #666666">{</span>
            dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">new</span> Account<span style="color: #666666">();</span>
            <span style="color: #B00040">int</span> columnIndex <span style="color: #666666">=</span> <span style="color: #666666">0;</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setId</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getLong</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setFirstName</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setLastName</span><span style="color: #666666">(</span>cursor<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span>columnIndex<span style="color: #666666">++));</span>
        <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">else</span> <span style="color: #666666">{</span>
            dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">finally</span> <span style="color: #666666">{</span>
        cursor<span style="color: #666666">.</span><span style="color: #7D9029">close</span><span style="color: #666666">();</span>
    <span style="color: #666666">}</span>

    assertNull<span style="color: #666666">(</span>dbAccount<span style="color: #666666">);</span>
<span style="color: #666666">}</span>
</pre></div>


		<p></p>
		<p>As you see, how simple the implementation by SQLiteExecutor is. Here are the steps to program with SQLiteExecutor: </p>
		<ul>
			<li>Prepare the database and tables with the consistent naming convention. Any java types not supported by SQLite should be defined as 'TEXT'.</li>			
			
			<p></p>
			<li>Generate the entity objects by <a href="./code-generation.html">Code Generation</a>.</li>
			
			<p></p>
			<li>You will be ready to operate data by Entity/Map directly and query by sql. Parameterized SQL with named parameters is supported as well.</li>
		</ul>		
		

		</div>

	</div>
</body>

</html>